﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;

using System.Web.Configuration;
using System.Data;
using System.Diagnostics;
using MySql;
using MySql.Data;
using MySql.Data.MySqlClient;

using System.Text.RegularExpressions;
using System.Collections.Specialized;
using System.Web.Security;
using System.Security.Principal;

using System.Net;
using System.Net.Mail;

namespace doris.Agent {
    public partial class Reply : System.Web.UI.Page {
        public class DorisUser {
            public String CustID { get; set; }
            public String Username { get; set; }
            public String Password { get; set; }
            public int Exists { get; set; }
        }

        public class TicketStatus {
            public int TicketID { get; set; }
            public String Service { get; set; }
            public String Priority { get; set; }
            public String Status { get; set; }
        }
        public class MAILConnection {
            public String ConnectionName { get; set; }
            public String Server { get; set; }
            public Boolean UseCredentials { get; set; }
            public String Username { get; set; }
            public String Password { get; set; }
            public int Port { get; set; }
            public Boolean UseSSL { get; set; }
            public Boolean Enabled { get; set; }
        }
        public MAILConnection FetchSMTP() {
            MAILConnection smtp = new MAILConnection();
            MySqlConnection conn = new MySqlConnection(WebConfigurationManager.ConnectionStrings["localConnectionString"].ConnectionString);
            try {
                conn.Open();
                MySqlCommand cmd = new MySqlCommand();
                cmd.Connection = conn;

                //          Console.WriteLine("Connecting to MySQL...");
                //                cmd.CommandText = "getTicketStatus";
                cmd.CommandText = "getEnabledSmtp";
                cmd.CommandType = CommandType.StoredProcedure;

                MySqlDataReader reader = cmd.ExecuteReader();

                if (reader.Read()) {
                    smtp.ConnectionName = reader.GetValue(1).ToString();
                    smtp.Server = reader.GetValue(2).ToString();
                    smtp.UseCredentials = (int.Parse(reader.GetValue(3).ToString()) == 1 ? true : false);
                    smtp.Username = reader.GetValue(4).ToString();
                    smtp.Password = reader.GetValue(5).ToString();
                    smtp.Port = int.Parse(reader.GetValue(6).ToString());
                    smtp.UseSSL = (int.Parse(reader.GetValue(7).ToString()) == 1 ? true : false);
                    smtp.Enabled = (int.Parse(reader.GetValue(8).ToString()) == 1 ? true : false);
                }

                cmd.Dispose();
            } catch (MySql.Data.MySqlClient.MySqlException ex) {
                Debug.WriteLine("Error " + ex.Number + " has occurred: " + ex.Message);
            } finally {
                conn.Close();
            }
            return smtp;
        }


        protected void storeCurrentStatus() {
            MySqlConnection conn = new MySqlConnection(WebConfigurationManager.ConnectionStrings["localConnectionString"].ConnectionString);
            try {
                conn.Open();
                MySqlCommand cmd = new MySqlCommand();
                cmd.Connection = conn;

                cmd.CommandText = "storeTicketStatus";
                cmd.CommandType = CommandType.StoredProcedure;

                cmd.Parameters.Add(new MySqlParameter("@in_ticketid", MySqlDbType.Int32));
                cmd.Parameters["@in_ticketid"].Value = int.Parse(Request.QueryString["id"]);
                cmd.Parameters["@in_ticketid"].Direction = ParameterDirection.Input;

                cmd.Parameters.Add(new MySqlParameter("@in_ticketstatusid", MySqlDbType.Int32));
                cmd.Parameters["@in_ticketstatusid"].Value = int.Parse(lStatus.SelectedValue);
                cmd.Parameters["@in_ticketstatusid"].Direction = ParameterDirection.Output;

                cmd.ExecuteNonQuery();

                cmd.Dispose();
            } catch (MySql.Data.MySqlClient.MySqlException ex) {
                Debug.WriteLine("Error " + ex.Number + " has occurred: " + ex.Message);
            } finally {
                conn.Close();
            }
        }

        public Boolean SendTicketResponse(String subject, String sMessage, bool reply) {
            MySqlConnection conn = new MySqlConnection(WebConfigurationManager.ConnectionStrings["localConnectionString"].ConnectionString);
            DorisUser doris = new DorisUser();
            TicketStatus data = new TicketStatus();
            String email = String.Empty;
            try {
                conn.Open();
                MySqlCommand cmd = new MySqlCommand();
                cmd.Connection = conn;

                cmd.CommandText = "getTicket";
                cmd.CommandType = CommandType.StoredProcedure;

                cmd.Parameters.Add(new MySqlParameter("@in_ticketid", MySqlDbType.Int32));
                cmd.Parameters["@in_ticketid"].Value = int.Parse(Request.QueryString["id"]);
                cmd.Parameters["@in_ticketid"].Direction = ParameterDirection.Input;

                MySqlDataReader reader = cmd.ExecuteReader();

                if (reader.Read()) {
                    data.TicketID = int.Parse(Request.QueryString["id"]);
                    data.Status   = lStatus.SelectedItem.Text;
                    data.Priority = reader["Priority"].ToString();
                    data.Service  = "Unimpemented";
                    doris.CustID   = reader["UserID"].ToString();
                    doris.Username = reader["Username"].ToString();
                    email          = reader["Email"].ToString();
                    doris.Password = "(Se tidligere epost)";
                }
                cmd.Dispose();
            } catch (MySql.Data.MySqlClient.MySqlException ex) {
                Debug.WriteLine("Error " + ex.Number + " has occurred: " + ex.Message);
            } finally {
                conn.Close();
            }
        
            // String sender, String email, String fullname
            String sender = "doris@mpdemo.no";
            String fullname = "";

            String Message = String.Empty;
            if (!reply) {
                // String subject, String fullname, String message, DorisUser doris, TicketStatus data, bool reply
                try {
                    conn.Open();
                    MySqlCommand cmd = new MySqlCommand();
                    cmd.Connection = conn;

                    //          Console.WriteLine("Connecting to MySQL...");
                    //                cmd.CommandText = "getTicketStatus";
                    cmd.CommandText = "getDefaultResponse";
                    cmd.CommandType = CommandType.StoredProcedure;

                    cmd.Parameters.Add(new MySqlParameter("@out_message", MySqlDbType.Text));
                    cmd.Parameters["@out_message"].Direction = ParameterDirection.Output;

                    cmd.ExecuteNonQuery();

                    Message = HttpUtility.HtmlDecode(cmd.Parameters["@out_message"].Value.ToString());
                    cmd.Dispose();


                } catch (MySql.Data.MySqlClient.MySqlException ex) {
                    Debug.WriteLine("Error " + ex.Number + " has occurred: " + ex.Message);
                } finally {
                    conn.Close();
                }

                Message = Message.Replace("[fullname]", fullname);
                Message = Message.Replace("[ticketid]", data.TicketID.ToString());
                Message = Message.Replace("[subject]", subject);
                Message = Message.Replace("[department]", data.Service);
                Message = Message.Replace("[priority]", data.Priority);
                Message = Message.Replace("[status]", data.Status);
                Message = Message.Replace("[url]", "URL");
                Message = Message.Replace("[username]", doris.Username);
                Message = Message.Replace("[password]", doris.Password);
            } else {
                Message = sMessage;
            }

            try {
                string MyFrom = sender;
                string MyTo = email;
                string MySubject = "[Ticket:" + data.TicketID + "] " + subject;
                string MyBody = Message;

                MAILConnection smtp = FetchSMTP();
                /*                string MySmtpMailServerName = "mail.broadpark.no";
                                int MySmtpMailServerPort = 25; //25 is the default for SMTP

                                SmtpClient MySmtpClient = new SmtpClient(MySmtpMailServerName, MySmtpMailServerPort);*/
                SmtpClient MySmtpClient = new SmtpClient(smtp.Server, smtp.Port);
                if (smtp.UseCredentials) {
                    MySmtpClient.Credentials = new NetworkCredential(smtp.Username, smtp.Password);
                }
                if (smtp.UseSSL) {
                    MySmtpClient.EnableSsl = true;
                }

                Debug.WriteLine("Sender epost fra " + smtp.Server + ", " + smtp.Port + " identifisert med " + smtp.Username + " og " + smtp.Password);
                //                MySmtpClient.Send(MyFrom, MyTo, MySubject, MyBody);

                /*
                 * Hent inn hvem som vil motta notifikasjon og ikke. FRITZ
                 * */
           /*     if (reply) {
                    mailAboutReply(MySubject, data.TicketID, MySmtpClient, MyFrom, doris, data);
                } else {
                    mailAboutNewMessage(MySubject, data.TicketID, MySmtpClient, MyFrom, doris, data);
                }*/
                /*
                |                   1 | NewQuestions |
                |                   2 | ReplyAll     |
                |                   3 | ReplyMine    |
                 */
                // * MailConfiguration




                using (MailMessage newMail = new MailMessage()) {
                    newMail.From = new MailAddress(MyFrom);
                    newMail.To.Add(MyTo);
                    newMail.Subject = MySubject;
                    newMail.AlternateViews.Add(AlternateView.CreateAlternateViewFromString(MyBody, null, "text/html"));
                    MySmtpClient.Send(newMail);
                }

                Debug.WriteLine("Message sent.");
            } catch (Exception MyError) {
                Debug.WriteLine("An error has occurred: " + MyError.Message);
                return false;
            }
            Debug.WriteLine("Done sending through smtp");
            return true;
        }

        protected void storeNewMessage() {    
            MySqlConnection conn = new MySqlConnection(WebConfigurationManager.ConnectionStrings["localConnectionString"].ConnectionString);
            try {
                conn.Open();
                MySqlCommand cmd = new MySqlCommand();
                cmd.Connection = conn;
                cmd.CommandText = "addTicketReply";
                cmd.CommandType = CommandType.StoredProcedure;
                IPrincipal currentUser = HttpContext.Current.User;


                cmd.Parameters.Add(new MySqlParameter("@in_ticketid", MySqlDbType.Int32));
                cmd.Parameters["@in_ticketid"].Value = int.Parse(Request.QueryString["id"]);
                cmd.Parameters["@in_ticketid"].Direction = ParameterDirection.Input;

                cmd.Parameters.Add(new MySqlParameter("@in_title", MySqlDbType.VarChar));
                cmd.Parameters["@in_title"].Value = tTitle.Text; ;
                cmd.Parameters["@in_title"].Direction = ParameterDirection.Input;

                cmd.Parameters.Add(new MySqlParameter("@in_ticketpriorityid", MySqlDbType.Int32));
                cmd.Parameters["@in_ticketpriorityid"].Value = 1;
                cmd.Parameters["@in_ticketpriorityid"].Direction = ParameterDirection.Input;

                cmd.Parameters.Add(new MySqlParameter("@in_message", MySqlDbType.Text));
                cmd.Parameters["@in_message"].Value = Editor.Text;
                cmd.Parameters["@in_message"].Direction = ParameterDirection.Input;

                cmd.Parameters.Add(new MySqlParameter("@in_agentusername", MySqlDbType.VarChar));
                cmd.Parameters["@in_agentusername"].Value = currentUser.Identity.Name;
                cmd.Parameters["@in_agentusername"].Direction = ParameterDirection.Input;

                cmd.Parameters.Add(new MySqlParameter("@in_workminutes", MySqlDbType.Int32));
                cmd.Parameters["@in_workminutes"].Direction = ParameterDirection.Input;

                cmd.Parameters.Add(new MySqlParameter("@in_parentticketdataid", MySqlDbType.Int32));
//                cmd.Parameters["@in_parentticketdataid"].Value = int.Parse(Request.QueryString["id"]);
                cmd.Parameters["@in_parentticketdataid"].Direction = ParameterDirection.Input;

                cmd.Parameters.Add(new MySqlParameter("@in_tickedassignedid", MySqlDbType.Int32));
            //    cmd.Parameters["@in_tickedassignedid"].Value = int.Parse(Request.QueryString["id"]);
                cmd.Parameters["@in_tickedassignedid"].Direction = ParameterDirection.Input;

                cmd.Parameters.Add(new MySqlParameter("@out_ticketdataid", MySqlDbType.Int32));
                cmd.Parameters["@out_ticketdataid"].Direction = ParameterDirection.Output;

                cmd.ExecuteNonQuery();

              //  statusid = int.Parse(cmd.Parameters["@out_ticketstatusid"].Value.ToString());
                //                lnkReply.NavigateUrl = "Reply.aspx?id=" + Request.QueryString["id"] + "&d=" + Request.QueryString["d"];
                cmd.Dispose();
            } catch (MySql.Data.MySqlClient.MySqlException ex) {
                Debug.WriteLine("Error " + ex.Number + " has occurred: " + ex.Message);
            } finally {
                conn.Close();
            }
        }

        protected void getStatus(int currStatus) {
            MySqlConnection conn = new MySqlConnection(WebConfigurationManager.ConnectionStrings["localConnectionString"].ConnectionString);
            try {
                conn.Open();
                MySqlCommand cmd = new MySqlCommand();
                cmd.Connection = conn;

                cmd.CommandText = "getAllTicketStatus";
                cmd.CommandType = CommandType.StoredProcedure;

                MySqlDataReader reader = cmd.ExecuteReader();

                //                int iCnt = 0;
                //                int iSel = 0;
                //                iSel = iCnt;
                while (reader.Read()) {
//                    ListItem li = new ListItem(reader["Status"].ToString(), reader["TicketStatusID"].ToString());
                    lStatus.Items.Add(new ListItem(reader["Status"].ToString(), reader["TicketStatusID"].ToString()));
                    if (int.Parse(reader["TicketStatusID"].ToString()) == currStatus) {
                        lStatus.Items[lStatus.Items.Count-1].Selected = true;
                    } 
                    //                    iCnt++;
                }

                cmd.Dispose();
            } catch (MySql.Data.MySqlClient.MySqlException ex) {
                Debug.WriteLine("Error " + ex.Number + " has occurred: " + ex.Message);
            } finally {
                conn.Close();
            }
        }

        protected int getCurrentStatus() {
            MySqlConnection conn = new MySqlConnection(WebConfigurationManager.ConnectionStrings["localConnectionString"].ConnectionString);
            int statusid = 0;
            try {
                conn.Open();
                MySqlCommand cmd = new MySqlCommand();
                cmd.Connection = conn;

                cmd.CommandText = "getTicketStatus";
                cmd.CommandType = CommandType.StoredProcedure;

                cmd.Parameters.Add(new MySqlParameter("@in_ticketid", MySqlDbType.Int32));
                cmd.Parameters["@in_ticketid"].Value = int.Parse(Request.QueryString["id"]);
                cmd.Parameters["@in_ticketid"].Direction = ParameterDirection.Input;

                cmd.Parameters.Add(new MySqlParameter("@out_ticketstatusid", MySqlDbType.Int32));
                cmd.Parameters["@out_ticketstatusid"].Direction = ParameterDirection.Output;

                cmd.Parameters.Add(new MySqlParameter("@out_status", MySqlDbType.VarChar));
                cmd.Parameters["@out_status"].Direction = ParameterDirection.Output;

                cmd.Parameters.Add(new MySqlParameter("@out_ticketstatusid", MySqlDbType.VarChar));
                cmd.Parameters["@out_status"].Direction = ParameterDirection.Output;
                cmd.ExecuteNonQuery();

                statusid = int.Parse(cmd.Parameters["@out_ticketstatusid"].Value.ToString());
                //                lnkReply.NavigateUrl = "Reply.aspx?id=" + Request.QueryString["id"] + "&d=" + Request.QueryString["d"];
                cmd.Dispose();
            } catch (MySql.Data.MySqlClient.MySqlException ex) {
                Debug.WriteLine("Error " + ex.Number + " has occurred: " + ex.Message);
            } finally {
                conn.Close();
            }
            return statusid;
        }

        protected void Page_Load(object sender, EventArgs e) {
            if (Request.QueryString["id"] == null) {
                Response.Redirect("/Agent/Default.aspx");
            }
            if (Page.IsPostBack) {
                storeNewMessage();
                storeCurrentStatus();
                SendTicketResponse(tTitle.Text, Editor.Text, true);

                if (Request.QueryString["d"] != null) {
                    Response.Redirect("Ticket.aspx?id=" + Request.QueryString["id"] + "&d=" + Request.QueryString["d"]);
                } else if (Request.QueryString["id"] != null) {
                    Response.Redirect("Ticket.aspx?id=" + Request.QueryString["id"]);
                } else {
                    Response.Redirect("Default.aspx");
                }
            }

            if (Request.QueryString["id"] != null) {
                int currStatus = getCurrentStatus();
                getStatus(currStatus);
            }
            if (Request.QueryString["d"] != null) {
          //      LiteralControl objMessage = Message.Controls[0] as LiteralControl;

                MySqlConnection conn = new MySqlConnection(WebConfigurationManager.ConnectionStrings["localConnectionString"].ConnectionString);
                try {
                    conn.Open();
                    MySqlCommand cmd = new MySqlCommand();
                    cmd.Connection = conn;

                    cmd.CommandText = "getTicketDataByID";
                    cmd.CommandType = CommandType.StoredProcedure;

                    cmd.Parameters.Add(new MySqlParameter("@in_ticketdataid", MySqlDbType.Int32));
                    cmd.Parameters["@in_ticketdataid"].Value = int.Parse(Request.QueryString["d"]);
                    cmd.Parameters["@in_ticketdataid"].Direction = ParameterDirection.Input;

                    cmd.Parameters.Add(new MySqlParameter("@out_ticketid", MySqlDbType.Int32));
                    cmd.Parameters["@out_ticketid"].Direction = ParameterDirection.Output;

                    cmd.Parameters.Add(new MySqlParameter("@out_createddate", MySqlDbType.DateTime));
                    cmd.Parameters["@out_createddate"].Direction = ParameterDirection.Output;

                    cmd.Parameters.Add(new MySqlParameter("@out_updateddate", MySqlDbType.DateTime));
                    cmd.Parameters["@out_updateddate"].Direction = ParameterDirection.Output;

                    cmd.Parameters.Add(new MySqlParameter("@out_title", MySqlDbType.VarChar));
                    cmd.Parameters["@out_title"].Direction = ParameterDirection.Output;

                    cmd.Parameters.Add(new MySqlParameter("@out_message", MySqlDbType.Text));
                    cmd.Parameters["@out_message"].Direction = ParameterDirection.Output;

                    cmd.Parameters.Add(new MySqlParameter("@out_ticketdatatypeid", MySqlDbType.Int32));
                    cmd.Parameters["@out_ticketdatatypeid"].Direction = ParameterDirection.Output;

                    cmd.Parameters.Add(new MySqlParameter("@out_type", MySqlDbType.VarChar));
                    cmd.Parameters["@out_type"].Direction = ParameterDirection.Output;

                    cmd.Parameters.Add(new MySqlParameter("@out_workminutes", MySqlDbType.VarChar));
                    cmd.Parameters["@out_workminutes"].Direction = ParameterDirection.Output;

                    cmd.Parameters.Add(new MySqlParameter("@out_agentid", MySqlDbType.VarChar));
                    cmd.Parameters["@out_agentid"].Direction = ParameterDirection.Output;

                    cmd.Parameters.Add(new MySqlParameter("@out_tickedassignedid", MySqlDbType.Int32));
                    cmd.Parameters["@out_tickedassignedid"].Direction = ParameterDirection.Output;

                    cmd.Parameters.Add(new MySqlParameter("@out_parentticketdataid", MySqlDbType.Int32));
                    cmd.Parameters["@out_parentticketdataid"].Direction = ParameterDirection.Output;

                    cmd.ExecuteNonQuery();

                    Editor.Text = "<blockquote>" + cmd.Parameters["@out_message"].Value.ToString() + "</blockquote><br />";
                    //                    objMessage.Text = "<pre>" + cmd.Parameters["@out_message"].Value.ToString() + "</pre>";
                    if ((cmd.Parameters["@out_title"].Value.ToString()).Substring(0, 2).ToLower() == "re")
                        tTitle.Text = cmd.Parameters["@out_title"].Value.ToString();
                    else     
                        tTitle.Text = "Re: " + cmd.Parameters["@out_title"].Value.ToString();
                    cmd.Dispose();
                } catch (MySql.Data.MySqlClient.MySqlException ex) {
                    Debug.WriteLine("Error " + ex.Number + " has occurred: " + ex.Message);
                } finally {
                    conn.Close();
                }
            } else if (Request.QueryString["id"] != null) {
       //         LiteralControl objMessage = Message.Controls[0] as LiteralControl;
                MySqlConnection conn = new MySqlConnection(WebConfigurationManager.ConnectionStrings["localConnectionString"].ConnectionString);
                try {
                    conn.Open();
                    MySqlCommand cmd = new MySqlCommand();
                    cmd.Connection = conn;

                    cmd.CommandText = "getTicketDataByTicketID";
                    cmd.CommandType = CommandType.StoredProcedure;

                    cmd.Parameters.Add(new MySqlParameter("@in_ticketid", MySqlDbType.Int32));
                    cmd.Parameters["@in_ticketid"].Value = int.Parse(Request.QueryString["id"]);
                    cmd.Parameters["@in_ticketid"].Direction = ParameterDirection.Input;

                    cmd.Parameters.Add(new MySqlParameter("@out_ticketdataid", MySqlDbType.Int32));
                    cmd.Parameters["@out_ticketdataid"].Direction = ParameterDirection.Output;

                    cmd.Parameters.Add(new MySqlParameter("@out_createddate", MySqlDbType.DateTime));
                    cmd.Parameters["@out_createddate"].Direction = ParameterDirection.Output;

                    cmd.Parameters.Add(new MySqlParameter("@out_updateddate", MySqlDbType.DateTime));
                    cmd.Parameters["@out_updateddate"].Direction = ParameterDirection.Output;

                    cmd.Parameters.Add(new MySqlParameter("@out_title", MySqlDbType.VarChar));
                    cmd.Parameters["@out_title"].Direction = ParameterDirection.Output;

                    cmd.Parameters.Add(new MySqlParameter("@out_message", MySqlDbType.Text));
                    cmd.Parameters["@out_message"].Direction = ParameterDirection.Output;

                    cmd.Parameters.Add(new MySqlParameter("@out_ticketdatatypeid", MySqlDbType.Int32));
                    cmd.Parameters["@out_ticketdatatypeid"].Direction = ParameterDirection.Output;

                    cmd.Parameters.Add(new MySqlParameter("@out_type", MySqlDbType.VarChar));
                    cmd.Parameters["@out_type"].Direction = ParameterDirection.Output;

                    cmd.Parameters.Add(new MySqlParameter("@out_workminutes", MySqlDbType.VarChar));
                    cmd.Parameters["@out_workminutes"].Direction = ParameterDirection.Output;

                    cmd.Parameters.Add(new MySqlParameter("@out_agentid", MySqlDbType.VarChar));
                    cmd.Parameters["@out_agentid"].Direction = ParameterDirection.Output;

                    cmd.Parameters.Add(new MySqlParameter("@out_tickedassignedid", MySqlDbType.Int32));
                    cmd.Parameters["@out_tickedassignedid"].Direction = ParameterDirection.Output;

                    cmd.Parameters.Add(new MySqlParameter("@out_parentticketdataid", MySqlDbType.Int32));
                    cmd.Parameters["@out_parentticketdataid"].Direction = ParameterDirection.Output;

                    cmd.ExecuteNonQuery();

//                    objMessage.Text = "<pre>" + cmd.Parameters["@out_message"].Value.ToString() + "</pre>";
                    Editor.Text = "<blockquote>" + cmd.Parameters["@out_message"].Value.ToString() + "</blockquote><br />";

                    cmd.Dispose();
                } catch (MySql.Data.MySqlClient.MySqlException ex) {
                    Debug.WriteLine("Error " + ex.Number + " has occurred: " + ex.Message);
                } finally {
                    conn.Close();
                }
            }

        }
    }
}